How to Create Auto-Backup Database

TGoldenEye

How to Create Auto-Backup Database

In this How To, I will demonstrate how to automatically create a backup file of your database. I will use a copy method that will copy the current file to a new location. We can set it to backup the file when the database file is opened or closed.

It can occur due to the regular intake of pills helps women who are unable to fantasize in love or viagra properien have a kind of repulsion towards their partner. It is in jelly form which helps super generic cialis by enforcing the blood to flow at a faster rate to fill in the penis, which causes erection. ‘Vardenafil’ is the main ingredient used to treat this sexual problem. Musculoskeletal pain usually affects the support structures of your body like bones, muscles, ligaments, levitra generic vardenafil and tendons that allows you to move and perform your daily duties effectively. For example, viagra pills wholesale in the Oprah.com online community, the berry is described as the number one super food.

Create Function

  • Under the Create Menu, click the Module icon

  • The Microsoft Visual Basic application will open

  • Name the function as CreateBackup()
  • Enter the VB code shown below inside the function
  • Save the Module as fBackup after you are done

Full Code:
Public Function CreateBackup() As Boolean
Dim Source As String
Dim Target As String
Dim a As Integer
Dim objFSO As Object
Dim Path As String
Path = CurrentProject.Path  'get location of current folder
Source = CurrentDb.Name
Target = Path & "\BackupDB "
Target = Target & Format(Now(), "mm-dd") & ".accdb"

' create the backup
a = 0
Set objFSO = CreateObject("Scripting.FileSystemObject")
a = objFSO.CopyFile(Source, Target, True)
Set objFSO = Nothing
End Function
Explanation:
  • Path = CurrentProject.Path        = the location of the current folder that contains the Access database file
  • Source = CurrentDb.Name         = the current Access database file
  • Target = Path & “\BackupDB ” = the new file location to be saved to, and the file name starts with BackupDB. The backup location is the same as the current file in this example.
  • Format(Now(), “mm-dd”) & “.accdb” = name the new file to end with the month-day (Ex: 08-24) and set the file type to .accdb after BackupDB
  • Set objFSO = CreateObject(“Scripting.FileSystemObject”) = allow the computer system to create a new file
  • CopyFile(Source, Target, True) = the computer system will copy the current file (Source) to the assigned location (Target)

The Scripting.FileSystemObject is used to gain an access to a computer’s file system. It can create new files, folders, directory paths, and access existing ones. The FileSystemObject has many usable methods such as CopyFile, DeleteFile, CopyFolder etc.

More information about FileSystemObject here

Apply Function

 1.Backup When File Opens

We can set the system to make a copy of the current Access database file by calling for the CreateBackup() function. In this example, I set the Navigation Form under the Access Options from More Commands… of the Customize Quick Access Toolbar to be displayed after I open this file. See the picture below.

Under the Form Design View, we need to select the Event Procedure under the On Load Event of the Form property, and then click on three dots (…) next to the Event Procedure.

After the Microsoft Visual Basic application opens, you will call for the CreateBackup() function under the Fom_Load() procedure, as shown below.

When you open the Access database file, the file you set to display under the Access Options above will open, in this case, the Navigation Form. While the Navigation Form is loading, the computer system will make a copy of this file and rename it as specified in the CreateBackup() function.

After you open the current folder of your current Access file, you will see the backup of your Access file with the name, in this example, “BackupDB 08-08”, as shown below.

2.Backup When File Closed

 We can set the system to make a copy of the current Access database file when the Access program is closed or unloaded. Under the Form Design View, we need to:

  • In the Form property, select the Event Procedure under the On Close Event or the On Unload Event
  • Click on the three dots (…) next to the Event Procedure.

  • Call the CreateBackup() function under the Fom_Close() procedure as shown below.

       

Modify Function

The CreateBackup() function is working perfectly; however, I will show you how to change the name and location of the backup file. The example below is my original code. It creates 1 backup file per day because the file name ends with the current month and date (08-08 for today’s date of August 8, 2020). If you open and close the Access file many times a day, the backup file will override the old file from that day only. However, if you use the Access file every day for a year you will still have 365 backup files in your folder.

VBA Code:
Path = CurrentProject.Path 
Source = CurrentDb.Name
Target = Path & "\BackupDB "
Target = Target & Format(Now(), "mm-dd") & ".accdb"
Result:

Path = “C:\Users\Tewan\Desktop\How to Create Auto-Backup”
Source = How to Create Auto-Backup DB.accdb
Target = “C:\Users\Tewan\Desktop\How to Create Auto-Backup\BackupDB “
Target = “C:\Users\Tewan\Desktop\How to Create Auto-Backup\BackupDB 08-08.accdb”

I will illustrate more examples of changing the Path (the destination of backup file) and Target (the name of new file) in the VB code below.

VBA Code: 
Path = "C:\TestDB"
Target = Path & "\BackupDB " & Format(Now(), "mm-dd hh") & ".accdb"
Result:

Path = “C:\TestDB”
Target = “C:\TestDB\BackupDB 08-08 12.accdb”   (created a backup on August 8, 2020 at 12:15 PM)

 

VBA Code: 
Path = "C:\TestDB"
Target = Path & "\BackupDB " & Format(Now(), "mm-dd hh_mm AM/PM") & ".accdb"
Result:

Path = “C:\TestDB”
Target = “C:\TestDB\BackupDB 08-09 12_20 PM.accdb”   (created a backup on August 9, 2020 at 12:20 PM)

 

VBA Code: 
Path = "C:\TestDB"
Target = Path & "\BackupDB_" & Format(Now(), "mm") & ".accdb"
Result:

Path = “C:\TestDB”
Target = “C:\TestDB\BackupDB_08.accdb”   (created a backup on August 9, 2000 at 12:20 PM)

 

VBA Code: 
Path = "C:\TestDB"
Target = Path & "\BackupDB.accdb"
Result:

Path = “C:\TestDB”
Target = “C:\TestDB\BackupDB.accdb”   (Save the backup on one file only. The new file will override the old file)

You must have a folder called “TestDB” in your computer’s C drive (drive C:) for the Path = “C:\TestDB”, otherwise you will get an error as shown below.

Now we can add more codes to test if the assigned folder exists and to create the folder before creating the backup file. I will use the FileSystemObject method to test or create the folder inside the If…Statement as shown below. By doing this we won’t get an error.

If objFSO.FolderExists(Path) Then    
    a = objFSO.CopyFile(Source, Target, True)
Else
    objFSO.createfolder (Path)
    a = objFSO.CopyFile(Source, Target, True)
End If

You will see the new full code below.

Public Function CreateBackup() As Boolean
Dim Source As String
Dim Target As String
Dim a As Integer
Dim objFSO As Object
Dim Path As String

'Path = CurrentProject.Path  'get location of current folder
Path = "C:\TestDB"
Source = CurrentDb.Name
Target = Path & "\BackupDB " & Format(Now(), "mm-dd hh_MM AM/PM") & ".accdb"

'create the backup
a = 0
Set objFSO = CreateObject("Scripting.FileSystemObject")
If objFSO.FolderExists(Path) Then  ‘Test if the folder exists before making a copy
    a = objFSO.CopyFile(Source, Target, True)
Else
    objFSO.createfolder (Path)    ‘Create folder if one does not exist
    a = objFSO.CopyFile(Source, Target, True)
End If
Set objFSO = Nothing
End Function

Related Video:

Related posts